import sys
import os
import codecs
import csv
import xlrd
import xlwt
import re
import numpy as np
import pandas as pd
import shutil
import requests
import moviepy as mp
from moviepy.editor import AudioFileClip
import time
import datetime
import random
import json
import logging
import logging.config
import traceback
import threading
import multiprocessing
import urllib.parse
import ffmpeg


# use yield to read file
def read_init_file(filename: str):
    with open(filename, "r") as f:
        for line in f:
            if line:
                yield line
            else:
                return None


def write_init_file(filename: str, content: str):
    with open(filename, "w") as f:
        f.write


def read_xlsx_file(xlsx_filename):
    # 打开文件
    workbook = xlrd.open_workbook(xlsx_filename)
    # 获取所有sheet
    print(workbook.sheet_names())  # [u'sheet1', u'sheet2']
    sheet2_name = workbook.sheet_names()[0]
    # 根据sheet索引或者名称获取sheet内容
    sheet2 = workbook.sheet_by_index(0)  # sheet索引从0开始
    # sheet2 = workbook.sheet_by_name('sheet2')
    # sheet的名称，行数，列数
    print(sheet2.name, sheet2.nrows, sheet2.ncols)
    # get all rows and cols, write them to csv file and download file at last column of each row
    with codecs.open(xlsx_filename+'.csv', 'w', 'utf-8') as csvfile:
        writer = csv.writer(csvfile)
        for i in range(0, sheet2.nrows):
            row = sheet2.row_values(i)
            writer.writerow(row[:-1])
            download_url = row[-1]
            if download_url.startswith("http"):
                # print("downloading", download_url)
                # r = requests.get(download_url)
                if os.path.exists("exp/{}.mp4".format(row[0])):
                    raw_mp4_filename = 'exp/{}.mp4'.format(row[0])
                    new_mp4_filename = 'exp/{}_{}.mp4'.format(row[3], row[0])
                    os.rename(raw_mp4_filename, new_mp4_filename)
                    wav_filename = 'exp/{}_{}.wav'.format(row[3], row[0])
                    audio_clip = AudioFileClip(new_mp4_filename)
                    audio_clip.write_audiofile(
                        wav_filename, fps=16000, nbytes=2, codec='pcm_s16le', bitrate='256k')
                # with open("exp/{}.mp4".format(row[0]), 'wb') as f:
                #     f.write(r.content)

    # rows = sheet2.row_values(3)  # 获取第四行内容
    # cols = sheet2.col_values(2)  # 获取第三列内容
    # print(rows)
    # print(cols)
    # # 获取单元格内容
    # print(sheet2.cell(1, 0).value.encode('utf-8'))
    # print(sheet2.cell_value(1, 0).encode('utf-8'))
    # print(sheet2.row(1)[0].value.encode('utf-8'))
    # # 获取单元格内容的数据类型
    # print(sheet2.cell(1, 0).ctype)
    # # 将数据写入新文件
    # new_workbook = xlwt.Workbook()
    # new_sheet = new_workbook.add_sheet('sheet1')
    # for i in range(0,len(rows)):
    #     new_sheet.write(0,i,rows[i])
    # new_workbook.save('new_'+xlsx_filename)
    # # 将数据写入csv文件
    # csvfile = open('csv_'+xlsx_filename+'.csv', 'w', newline='')
    # writer = csv.writer(csvfile)
    # writer.writerow(rows)
    # csvfile.close()
    # # 将数据写入txt文件
    # txtfile = open('txt_'+xlsx_filename+'.txt', 'w')
    # for i in range(0,len(rows)):
    #     txtfile.write(rows[i]+'\n')
    # txtfile.close()
    # # 将数据写入txt文件
    # txtfile = open('txt_'+xlsx_filename+'.txt', 'w')
    # for i in range(0,len(rows)):
    #     txtfile.write(rows[i]+'\n')
    # txtfile.close()


def convert_mp4_to_wav(mp4_file_list):
    for mp4_file in mp4_file_list:
        wav_file = mp4_file.replace(".mp4", ".wav")
        if os.path.exists(wav_file):
            continue


# main
if __name__ == '__main__':
    xlsx_filename = "exp/第一批数据.xls"
    read_xlsx_file(xlsx_filename)
